<?php

namespace app\common\controller;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Exception;

/**
 * Excel识别和创建
 * Trait Excel
 * description: 此功能依赖于phpspreadsheet，安装：composer require phpoffice/phpspreadsheet
 * @author fuyelk <fuyelk@fuyelk.com>
 */
trait Excel
{
    /**
     * 解析导入的文件，支持csv、xls、xlsx
     * @param array $column 字段映射
     * example:
     * [
     *      'username' => '姓名',
     *      'gender' => '性别',
     *      'years' => '年龄',
     *      'mobile' => '手机号'
     * ]
     */
    protected function parseExcel($column = [])
    {
        if (empty($column) || !is_array($column)) {
            $this->error_json('请设置字段对应规则');
        }

        // 上传单文件
        if (!$this->request->isPost() || empty($file = request()->file())) $this->error_json('未上传任何文件');
        $file = array_shift($file);
        $filename = '';
        $file_root = dirname(dirname(dirname(__DIR__))) . '/public/uploads/files/';

        // 移动到框架应用根目录/public/uploads/files 目录下
        if($file){
            $info = $file->move($file_root);
            if($info){
                $filename = $info->getSaveName();
            }else{
                // 上传失败获取错误信息
                $this->error_json($file->getError());
            }
        }

        $filePath = str_replace('\\','/',$file_root . $filename);

        if (!is_file($filePath)) {
            $this->error_json('文件不存在');
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error_json('未知的文件类型');
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error_json('未知的数据类型');
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    if(is_object($val))  $val= $val->__toString();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($column[$k]) && $k !== '') {
                        $row[$column[$k]] = $v;
                    }
                }
                if ($row) {
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            $this->error_json($exception->getMessage());
        }
        if (!$insert) {
            $this->error_json('没有更新任何数据');
        }
        return $insert;
    }

    /**
     * 导出Excel
     * @param string $sheetName sheet名
     * @param array $column 字段名，仅保存该值中包含的字段
     * example: ['姓名', '性别', '年龄', '手机号']
     * @param array $data 数据
     */
    protected function exportExcel($sheetName = '', $column = [], $data = [])
    {
        $sheetName = empty($sheetName) ? 'sheet1' : $sheetName;
        if (empty($column)) $this->error_json('字段名不能为空');

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

		//设置sheet的名字
        $spreadsheet->getActiveSheet()->setTitle($sheetName);

        $col = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        $temp_col = $col;

		//设置第一行小标题
        foreach ($column as $col_name) {
            $sheet->setCellValue(array_shift($temp_col) . 1, $col_name);
        }

        // 创建更多数据
        $row = 2;
        foreach ($data as $line => $value) {
            $temp_col = $col;
            foreach ($column as $col_name) {
                $sheet->setCellValue(array_shift($temp_col) . $row, array_shift($value));
            }
            $row++;
        }

        $file_name = date('Y-m-d', time()) . rand(1000, 9999);
        $file_name = $file_name . ".xlsx";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $file_name . '"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
}
